---
title: "SQL-019: Retrieve appeal hearing level list by case"
summary: Retrieve appeal/hearing review-level entries for a legal case, with a leading blank row.
status: draft
owner: SAIF Documentation Pipeline
last_reviewed: 2026-08-21
library: SAIF.Application.Claim.Legal
reference_type: embedded
---

# SQL-019 Retrieve appeal hearing level list by case

Retrieves `APPEAL` rows for the current legal case, left-outer-joined to `REVIEW_LEVEL_CODE` for the description, unioned with a leading "Hearings Level" placeholder row from `DUAL`, for the `APEALNUMLEVELSQL` code-table lookup used by the case outcome/appeal maintenance UI.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-019 |
| **Operation type** | Read |
| **Execution method** | Inline SQL, runtime string concatenation (`clsCodeCollection.GetCodeCollection`) |
| **Database** | Oracle |
| **Command type** | Text |
| **Business object** | N/A (Application layer) |
| **Criteria property** | N/A (Application layer) |
| **Lifecycle method** | N/A (Application layer) |
| **Application class** | `clsOutcomeManager` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `CurrentCaseID` | N/A — concatenated literal, not a bind parameter* | Decimal (`.ToString`) | N/A | Legal case ID appended directly onto the `APEALNUMLEVELSQL` string via `+` concatenation rather than an Oracle bind variable — no SQL-injection risk in practice since the value is a `Decimal` property, but it deviates from the bind-variable pattern used elsewhere in this library*. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [APPEAL](../database-tables.md#appeal) | SELECT, JOIN | `APPEAL_SEQ_NUM`, `APPEAL_NUM`, `LEGAL_CASE_SEQ_NUM`, `REVIEW_LEVEL_CODE` |
| [REVIEW_LEVEL_CODE](../database-tables.md#review_level_code) | SELECT, JOIN (LEFT OUTER) | `CODE`, `CODE_DESCR` |
| [DUAL](../database-tables.md#dual) | SELECT | N/A (pseudo-table; placeholder blank row) |

## Sample SQL

```sql
Select '0', 'Hearings Level'
from dual
union
Select
  to_char(APPEAL.APPEAL_SEQ_NUM) as code,
  CODE_DESCR || decode(appeal.appeal_num, null, '', '-' || APPEAL.APPEAL_NUM) as description
From
  APPEAL LEFT OUTER JOIN REVIEW_LEVEL_CODE On REVIEW_LEVEL_CODE = CODE
Where
  APPEAL.LEGAL_CASE_SEQ_NUM = <CurrentCaseID>
```

`<CurrentCaseID>` denotes runtime `.ToString`-concatenation of the `CurrentCaseID` decimal property directly onto the constant string (`APEALNUMLEVELSQL + CurrentCaseID.ToString`) rather than an Oracle bind variable*.

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts. Statements marked with `**` could not be confirmed; treat
> as unknown until verified.

## Sources
- `SAIF.Application.Claim.Legal\LegalNet\clsOutcomeManager.vb`